#!/usr/bin/env python
# -*- coding: utf-8 -*-
# File  :provision_excel_model.py
# Time  :2025/5/14 17:16
# Email :fortunatedong@163.com
# Author:by Adonli
import re

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils import get_column_letter, range_boundaries
from config import ROOT_DIR
import os


class ProvisionExcelModel():
    def __init__(self, brand):
        self.brand = brand
        if self.brand == 'zi':
            self.brand_name = 'Zi'
        if self.brand == 'zong':
            self.brand_name = 'Zong'
        self.provision_excel_file_dir_path = os.path.join(ROOT_DIR,
                                                          f'Datas/business_continued_provision/{self.brand}_provision_file_temp', )

    def to_excel_file(self, provision_date=None,
                      invoicing_customer_name=None, latest_customer_name=None,
                      nvoicing_customer_province=None, nvoicing_customer_city=None,
                      installation_address_province=None, installation_address_city=None,
                      installation_address_district=None, address=None, brand=None,
                      customer_distributor=None, telephone=None, liaison=None,
                      budget_department=None, use_department=None, other_brand=None,
                      service_agent_name=None, row20_prepare_recommended_models_accessories=None,
                      row21_number=None, row21_amount=None, row23_delivery_date=None,
                      row23_declarations_number=None, remarks=None):

        if budget_department == '':
            budget_department = "办公室"
        if use_department == '':
            use_department = "办公室"

        # 创建一个新的 Excel 工作簿
        wb = Workbook()
        ws = wb.active  # 获取当前活动的工作表
        ws.sheet_view.showGridLines = False

        # 设置列宽（单位：字符宽度）
        column_widths = {
            'A': 1.67, 'B': 24.25, 'C': 7.67, 'D': 6, 'E': 7.67,
            'F': 7.42, 'G': 7.67, 'H': 6.5, 'I': 12.75, 'J': 12.75,
            'K': 5, 'L': 14.5, 'M': 5, 'N': 8.25
        }

        for col, width in column_widths.items():
            ws.column_dimensions[col].width = width

        # 设置行高（单位：磅）
        row_heights = {
            1: 14, 2: 24, 3: 21, 4: 24, 5: 21,
            6: 21, 7: 21, 8: 21, 9: 21, 10: 21,
            11: 12, 12: 12, 13: 21, 14: 21, 15: 21,
            16: 12, 17: 12, 18: 21, 19: 21, 20: 25,
            21: 21, 22: 21, 23: 21, 24: 12, 25: 12, 26: 52
        }

        for row, height in row_heights.items():
            ws.row_dimensions[row].height = height

        # 设置第一行 B1:N1 合并居中
        ws.merge_cells('B1:N1')
        first_row_cell = ws['B1']
        first_row_cell.value = "报备申请表"
        first_row_cell.font = Font(name='宋体', size=11, color='FF000000')  # 黑色
        first_row_cell.alignment = Alignment(horizontal='center', vertical='center')

        # 设置第二行 C2:N2 合并居左
        invoicing_customer_name_cell = ws['B2']
        invoicing_customer_name_cell.value = "开票客户名称\n（请填写工商登记的客户名）"
        invoicing_customer_name_cell.font = Font(name='宋体', size=9, color="FFFF0000", bold=True)
        invoicing_customer_name_cell.alignment = Alignment(wrap_text=True, horizontal='center', vertical='center')
        ws.merge_cells('C2:N2')
        invoicing_customer_name_cell_value = ws['C2']
        invoicing_customer_name_cell_value.value = f"{invoicing_customer_name}"
        invoicing_customer_name_cell_value.font = Font(name='宋体', size=9, color="FF000000")
        invoicing_customer_name_cell_value.alignment = Alignment(horizontal='left', vertical='center')
        # 设置第三行
        si_customer_name_cell = ws['B3']
        si_customer_name_cell.value = "是否SI客户"
        si_customer_name_cell.font = Font(name='宋体', size=9, color="FFFF0000", bold=True)
        si_customer_name_cell.alignment = Alignment(horizontal='center', vertical='center')

        si_customer_name_cell_value_c3 = ws['C3']
        si_customer_name_cell_value_c3.value = "（ ☑ ）"
        si_customer_name_cell_value_c3.font = Font(name='宋体', size=9, color="FF000000")
        si_customer_name_cell_value_c3.alignment = Alignment(horizontal='center', vertical='center')
        si_customer_name_cell_value_d3 = ws['D3']
        si_customer_name_cell_value_d3.value = "否"
        si_customer_name_cell_value_d3.font = Font(name='宋体', size=9, color="FF000000")
        si_customer_name_cell_value_d3.alignment = Alignment(horizontal='center', vertical='center')
        si_customer_name_cell_value_e3 = ws['E3']
        si_customer_name_cell_value_e3.value = "（  ）"
        si_customer_name_cell_value_e3.font = Font(name='宋体', size=9, color="FF000000")
        si_customer_name_cell_value_e3.alignment = Alignment(horizontal='center', vertical='center')
        si_customer_name_cell_value_f3 = ws['F3']
        si_customer_name_cell_value_f3.value = "是"
        si_customer_name_cell_value_f3.font = Font(name='宋体', size=9, color="FF000000")
        si_customer_name_cell_value_f3.alignment = Alignment(horizontal='center', vertical='center')

        # 设置第四行
        latest_customer_name_cell = ws['B4']
        latest_customer_name_cell.value = "最终用户名称*\n（请填写工商登记的客户名）"
        latest_customer_name_cell.font = Font(name='宋体', size=9, color="FFFF0000", bold=True)
        latest_customer_name_cell.alignment = Alignment(wrap_text=True, horizontal='center', vertical='center')
        ws.merge_cells('C4:N4')
        latest_customer_name_cell_value = ws['C4']
        latest_customer_name_cell_value.value = f"{latest_customer_name}"
        latest_customer_name_cell_value.font = Font(name='宋体', size=9, color="FF000000")
        latest_customer_name_cell_value.alignment = Alignment(horizontal='left', vertical='center')
        # 设置第五行
        invoicing_customer_province_cell = ws['B5']
        invoicing_customer_province_cell.value = "开票客户所属省市 "
        invoicing_customer_province_cell.font = Font(name='宋体', size=9, color="FFFF0000", bold=True)
        invoicing_customer_province_cell.alignment = Alignment(horizontal='center', vertical='center')
        nvoicing_customer_province_cell_value_c5 = ws['C5']
        nvoicing_customer_province_cell_value_c5.value = f"（ {nvoicing_customer_province} ）"
        nvoicing_customer_province_cell_value_c5.font = Font(name='宋体', size=9, color="FF000000")
        nvoicing_customer_province_cell_value_c5.alignment = Alignment(horizontal='center', vertical='center')
        nvoicing_customer_province_cell_value_d5 = ws['D5']
        nvoicing_customer_province_cell_value_d5.value = "省"
        nvoicing_customer_province_cell_value_d5.font = Font(name='宋体', size=9, color="FF000000")
        nvoicing_customer_province_cell_value_d5.alignment = Alignment(horizontal='center', vertical='center')
        nvoicing_customer_province_cell_value_e5 = ws['E5']
        nvoicing_customer_province_cell_value_e5.value = f"（ {nvoicing_customer_city} ）"
        nvoicing_customer_province_cell_value_e5.font = Font(name='宋体', size=9, color="FF000000")
        nvoicing_customer_province_cell_value_e5.alignment = Alignment(horizontal='center', vertical='center')
        nvoicing_customer_province_cell_value_f5 = ws['F5']
        nvoicing_customer_province_cell_value_f5.value = "市"
        nvoicing_customer_province_cell_value_f5.font = Font(name='宋体', size=9, color="FF000000")
        nvoicing_customer_province_cell_value_f5.alignment = Alignment(horizontal='center', vertical='center')

        # 设置第六行
        installation_address_cell = ws['B6']
        installation_address_cell.value = "装机地址 "
        installation_address_cell.font = Font(name='宋体', size=9, color="FFFF0000", bold=True)
        installation_address_cell.alignment = Alignment(horizontal='center', vertical='center')

        installation_address_cell_value_c6 = ws['C6']
        installation_address_cell_value_c6.value = f"（ {installation_address_province} ）"
        installation_address_cell_value_c6.font = Font(name='宋体', size=9, color="FF000000")
        installation_address_cell_value_c6.alignment = Alignment(horizontal='center', vertical='center')
        installation_address_cell_value_d6 = ws['D6']
        installation_address_cell_value_d6.value = "省"
        installation_address_cell_value_d6.font = Font(name='宋体', size=9, color="FF000000")
        installation_address_cell_value_d6.alignment = Alignment(horizontal='center', vertical='center')
        installation_address_cell_value_e6 = ws['E6']
        installation_address_cell_value_e6.value = f"（ {installation_address_city} ）"
        installation_address_cell_value_e6.font = Font(name='宋体', size=9, color="FF000000")
        installation_address_cell_value_e6.alignment = Alignment(horizontal='center', vertical='center')
        installation_address_cell_value_f6 = ws['F6']
        installation_address_cell_value_f6.value = "市"
        installation_address_cell_value_f6.font = Font(name='宋体', size=9, color="FF000000")
        installation_address_cell_value_f6.alignment = Alignment(horizontal='center', vertical='center')
        installation_address_cell_value_g6 = ws['G6']
        installation_address_cell_value_g6.value = f"（ {installation_address_district} ）"
        installation_address_cell_value_g6.font = Font(name='宋体', size=9, color="FF000000")
        installation_address_cell_value_g6.alignment = Alignment(horizontal='center', vertical='center')
        installation_address_cell_value_h6 = ws['H6']
        installation_address_cell_value_h6.value = "区"
        installation_address_cell_value_h6.font = Font(name='宋体', size=9, color="FF000000")
        installation_address_cell_value_h6.alignment = Alignment(horizontal='center', vertical='center')

        address_cell = ws['I6']
        address_cell.value = "地址 "
        address_cell.font = Font(name='宋体', size=9, color="FFFF0000", bold=True)
        address_cell.alignment = Alignment(horizontal='center', vertical='center')
        ws.merge_cells('J6:N6')
        address_cell_value = ws['J6']
        address_cell_value.value = f"{address}"
        address_cell_value.font = Font(name='宋体', size=9, color="FF000000")
        address_cell_value.alignment = Alignment(wrap_text=True, horizontal='center', vertical='center')
        # 设置第七行
        sale_category_cell = ws['B7']
        sale_category_cell.value = "销售分类 "
        sale_category_cell.font = Font(name='宋体', size=9, color="FFFF0000", bold=True)
        sale_category_cell.alignment = Alignment(horizontal='center', vertical='center')

        sale_category_cell_value_c7 = ws['C7']
        sale_category_cell_value_c7.value = "（ ☑ ）"
        sale_category_cell_value_c7.font = Font(name='宋体', size=9, color="FF000000")
        sale_category_cell_value_c7.alignment = Alignment(horizontal='center', vertical='center')
        sale_category_cell_value_d7 = ws['D7']
        sale_category_cell_value_d7.value = "Dealer"
        sale_category_cell_value_d7.font = Font(name='宋体', size=9, color="FF000000")
        sale_category_cell_value_d7.alignment = Alignment(horizontal='center', vertical='center')
        sale_category_cell_value_e7 = ws['E7']
        sale_category_cell_value_e7.value = "（  ）"
        sale_category_cell_value_e7.font = Font(name='宋体', size=9, color="FF000000")
        sale_category_cell_value_e7.alignment = Alignment(horizontal='center', vertical='center')
        sale_category_cell_value_f7 = ws['F7']
        sale_category_cell_value_f7.value = "MA"
        sale_category_cell_value_f7.font = Font(name='宋体', size=9, color="FF000000")
        sale_category_cell_value_f7.alignment = Alignment(horizontal='center', vertical='center')
        sale_category_cell_value_g7 = ws['G7']
        sale_category_cell_value_g7.value = "（  ）"
        sale_category_cell_value_g7.font = Font(name='宋体', size=9, color="FF000000")
        sale_category_cell_value_g7.alignment = Alignment(horizontal='center', vertical='center')
        sale_category_cell_value_h7 = ws['H7']
        sale_category_cell_value_h7.value = "PP直销"
        sale_category_cell_value_h7.font = Font(name='宋体', size=9, color="FF000000")
        sale_category_cell_value_h7.alignment = Alignment(horizontal='center', vertical='center')

        customer_distributor_cell = ws['I7']
        customer_distributor_cell.value = "客户所属经销商 "
        customer_distributor_cell.font = Font(name='宋体', size=9, color="FFFF0000", bold=True)
        customer_distributor_cell.alignment = Alignment(horizontal='center', vertical='center')

        ws.merge_cells('J7:N7')
        customer_distributor_cell_value = ws['J7']
        customer_distributor_cell_value.value = f"{brand}-{customer_distributor}"
        customer_distributor_cell_value.font = Font(name='宋体', size=9, color="FF000000")
        customer_distributor_cell_value.alignment = Alignment(horizontal='center', vertical='center')

        # 设置第八行
        sale_scope_cell = ws['B8']
        sale_scope_cell.value = "销售分类 "
        sale_scope_cell.font = Font(name='宋体', size=9, color="FFFF0000", bold=True)
        sale_scope_cell.alignment = Alignment(horizontal='center', vertical='center')
        sale_scope_cell.alignment = Alignment(horizontal='center', vertical='center')
        sale_scope_cell_value_c8 = ws['C8']
        sale_scope_cell_value_c8.value = "（  ）"
        sale_scope_cell_value_c8.font = Font(name='宋体', size=9, color="FF000000")
        sale_scope_cell_value_c8.alignment = Alignment(horizontal='center', vertical='center')
        sale_scope_cell_value_d8 = ws['D8']
        sale_scope_cell_value_d8.value = "全国"
        sale_scope_cell_value_d8.font = Font(name='宋体', size=9, color="FF000000")
        sale_scope_cell_value_d8.alignment = Alignment(horizontal='center', vertical='center')
        sale_scope_cell_value_e8 = ws['E8']
        sale_scope_cell_value_e8.value = "（  ）"
        sale_scope_cell_value_e8.font = Font(name='宋体', size=9, color="FF000000")
        sale_scope_cell_value_e8.alignment = Alignment(horizontal='center', vertical='center')
        sale_scope_cell_value_f8 = ws['F8']
        sale_scope_cell_value_f8.value = "异地"
        sale_scope_cell_value_f8.font = Font(name='宋体', size=9, color="FF000000")
        sale_scope_cell_value_f8.alignment = Alignment(horizontal='center', vertical='center')
        sale_scope_cell_value_g8 = ws['G8']
        sale_scope_cell_value_g8.value = "（ ☑ ）"
        sale_scope_cell_value_g8.font = Font(name='宋体', size=9, color="FF000000")
        sale_scope_cell_value_g8.alignment = Alignment(horizontal='center', vertical='center')
        sale_scope_cell_value_h8 = ws['H8']
        sale_scope_cell_value_h8.value = "本地"
        sale_scope_cell_value_h8.font = Font(name='宋体', size=9, color="FF000000")
        sale_scope_cell_value_h8.alignment = Alignment(horizontal='center', vertical='center')

        government_procurement_cell = ws['I8']
        government_procurement_cell.value = "中央政府采购 "
        government_procurement_cell.font = Font(name='宋体', size=9, color="FFFF0000", bold=True)
        government_procurement_cell.alignment = Alignment(horizontal='center', vertical='center')
        government_procurement_cell_value_j8 = ws['J8']
        government_procurement_cell_value_j8.value = "（  ）"
        government_procurement_cell_value_j8.font = Font(name='宋体', size=9, color="FF000000")
        government_procurement_cell_value_j8.alignment = Alignment(horizontal='right', vertical='center')
        government_procurement_cell_value_k8 = ws['K8']
        government_procurement_cell_value_k8.value = "是"
        government_procurement_cell_value_k8.font = Font(name='宋体', size=9, color="FF000000")
        government_procurement_cell_value_k8.alignment = Alignment(horizontal='center', vertical='center')
        government_procurement_cell_value_l8 = ws['L8']
        government_procurement_cell_value_l8.value = "（ ☑ ）"
        government_procurement_cell_value_l8.font = Font(name='宋体', size=9, color="FF000000")
        government_procurement_cell_value_l8.alignment = Alignment(horizontal='right', vertical='center')
        government_procurement_cell_value_m8 = ws['M8']
        government_procurement_cell_value_m8.value = "否"
        government_procurement_cell_value_m8.font = Font(name='宋体', size=9, color="FF000000")
        government_procurement_cell_value_m8.alignment = Alignment(horizontal='center', vertical='center')

        # 设置第九行
        telephone_cell = ws['B9']
        telephone_cell.value = "固定电话 "
        telephone_cell.font = Font(name='宋体', size=9, color="FFFF0000", bold=True)
        telephone_cell.alignment = Alignment(horizontal='center', vertical='center')

        ws.merge_cells('C9:H9')
        telephone_cell_value = ws['C9']
        telephone_cell_value.value = f"{telephone}"
        telephone_cell_value.font = Font(name='宋体', size=9, color="FF000000")
        telephone_cell_value.alignment = Alignment(horizontal='center', vertical='center')

        liaison_cell = ws['I9']
        liaison_cell.value = "联系人 "
        liaison_cell.font = Font(name='宋体', size=9, color="FFFF0000", bold=True)
        liaison_cell.alignment = Alignment(horizontal='center', vertical='center')

        ws.merge_cells('J9:N9')
        liaison_cell_value = ws['J9']
        liaison_cell_value.value = f"{liaison}"
        liaison_cell_value.font = Font(name='宋体', size=9, color="FF000000")
        liaison_cell_value.alignment = Alignment(horizontal='center', vertical='center')

        # 设置第十行
        budget_department_cell = ws['B10']
        budget_department_cell.value = "预算部门 "
        budget_department_cell.font = Font(name='宋体', size=9, color="FFFF0000", bold=True)
        budget_department_cell.alignment = Alignment(horizontal='center', vertical='center')

        ws.merge_cells('C10:H10')
        budget_department_cell_value = ws['C10']
        budget_department_cell_value.value = f"{budget_department}"
        budget_department_cell_value.font = Font(name='宋体', size=9, color="FF000000")
        budget_department_cell_value.alignment = Alignment(horizontal='center', vertical='center')

        use_department_cell = ws['I10']
        use_department_cell.value = "使用部门 "
        use_department_cell.font = Font(name='宋体', size=9, color="FFFF0000", bold=True)
        use_department_cell.alignment = Alignment(horizontal='center', vertical='center')

        ws.merge_cells('J10:N10')
        use_department_cell_value = ws['J10']
        use_department_cell_value.value = f"{use_department}"
        use_department_cell_value.font = Font(name='宋体', size=9, color="FF000000")
        use_department_cell_value.alignment = Alignment(horizontal='center', vertical='center')

        # 设置第十一行

        # 设置第十二行
        current_machine_usage_cell = ws['B12']
        current_machine_usage_cell.value = "目前机器使用情况"
        current_machine_usage_cell.font = Font(name='宋体', size=9, color="FF000000", bold=True)
        # 设置黄色背景填充
        yellow_fill = PatternFill(start_color="FFFF00",  # 黄色
                                  end_color="FFFF00",
                                  fill_type="solid")
        current_machine_usage_cell.fill = yellow_fill

        # 设置第十三行
        brand_cell = ws['B13']
        brand_cell.value = "品牌 "
        brand_cell.font = Font(name='宋体', size=9, color="FFFF0000", bold=True)
        brand_cell.alignment = Alignment(horizontal='center', vertical='center')

        brand_cell_value_c13 = ws['C13']
        brand_cell_value_c13.value = "（ ☑ ）"
        brand_cell_value_c13.font = Font(name='宋体', size=9, color="FF000000")
        brand_cell_value_c13.alignment = Alignment(horizontal='center', vertical='center')
        brand_cell_value_d13 = ws['D13']
        brand_cell_value_d13.value = "Ricoh"
        brand_cell_value_d13.font = Font(name='宋体', size=9, color="FF000000")
        brand_cell_value_d13.alignment = Alignment(horizontal='center', vertical='center')
        brand_cell_value_e13 = ws['E13']
        brand_cell_value_e13.value = "（  ）"
        brand_cell_value_e13.font = Font(name='宋体', size=9, color="FF000000")
        brand_cell_value_e13.alignment = Alignment(horizontal='center', vertical='center')
        brand_cell_value_f13 = ws['F13']
        brand_cell_value_f13.value = "other"
        brand_cell_value_f13.font = Font(name='宋体', size=9, color="FF000000")
        brand_cell_value_f13.alignment = Alignment(horizontal='center', vertical='center')

        other_brand_cell = ws['I13']
        other_brand_cell.value = "其他品牌 "
        other_brand_cell.font = Font(name='宋体', size=9, color="FFFF0000", bold=True)
        other_brand_cell.alignment = Alignment(horizontal='center', vertical='center')

        other_brand_cell_value = ws['J13']
        other_brand_cell_value.value = f"{other_brand}"
        other_brand_cell_value.font = Font(name='宋体', size=9, color="FF000000")
        other_brand_cell_value.alignment = Alignment(horizontal='center', vertical='center')
        # 设置第十四行
        model_configuration_cell = ws['B14']
        model_configuration_cell.value = "机型/配置 "
        model_configuration_cell.font = Font(name='宋体', size=9, color="FFFF0000", bold=True)
        model_configuration_cell.alignment = Alignment(horizontal='center', vertical='center')

        model_configuration_cell_value = ws['D14']
        model_configuration_cell_value.value = "不详"
        model_configuration_cell_value.font = Font(name='宋体', size=9, color="FF000000")
        model_configuration_cell_value.alignment = Alignment(horizontal='center', vertical='center')

        is_rmif_cell = ws['I14']
        is_rmif_cell.value = "是否为RMIF "
        is_rmif_cell.font = Font(name='宋体', size=9, color="FFFF0000", bold=True)
        is_rmif_cell.alignment = Alignment(horizontal='center', vertical='center')

        is_rmif_cell_value_j14 = ws['J14']
        is_rmif_cell_value_j14.value = "（  ）"
        is_rmif_cell_value_j14.font = Font(name='宋体', size=9, color="FF000000")
        is_rmif_cell_value_j14.alignment = Alignment(horizontal='right', vertical='center')
        is_rmif_cell_value_k14 = ws['K14']
        is_rmif_cell_value_k14.value = "是"
        is_rmif_cell_value_k14.font = Font(name='宋体', size=9, color="FF000000")
        is_rmif_cell_value_k14.alignment = Alignment(horizontal='center', vertical='center')
        is_rmif_cell_value_l14 = ws['L14']
        is_rmif_cell_value_l14.value = "（ ☑ ）"
        is_rmif_cell_value_l14.font = Font(name='宋体', size=9, color="FF000000")
        is_rmif_cell_value_l14.alignment = Alignment(horizontal='right', vertical='center')
        is_rmif_cell_value_m14 = ws['M14']
        is_rmif_cell_value_m14.value = "否"
        is_rmif_cell_value_m14.font = Font(name='宋体', size=9, color="FF000000")
        is_rmif_cell_value_m14.alignment = Alignment(horizontal='center', vertical='center')

        # 设置第十五行
        service_provider_cell = ws['B15']
        service_provider_cell.value = "品牌 "
        service_provider_cell.font = Font(name='宋体', size=9, color="FFFF0000", bold=True)
        service_provider_cell.alignment = Alignment(horizontal='center', vertical='center')

        service_provider_cell_value_c15 = ws['C15']
        service_provider_cell_value_c15.value = "（  ）"
        service_provider_cell_value_c15.font = Font(name='宋体', size=9, color="FF000000")
        service_provider_cell_value_c15.alignment = Alignment(horizontal='center', vertical='center')
        service_provider_cell_value_d15 = ws['D15']
        service_provider_cell_value_d15.value = "Ricoh"
        service_provider_cell_value_d15.font = Font(name='宋体', size=9, color="FF000000")
        service_provider_cell_value_d15.alignment = Alignment(horizontal='center', vertical='center')
        service_provider_cell_value_e15 = ws['E15']
        service_provider_cell_value_e15.value = "（ ☑ ）"
        service_provider_cell_value_e15.font = Font(name='宋体', size=9, color="FF000000")
        service_provider_cell_value_e15.alignment = Alignment(horizontal='center', vertical='center')
        service_provider_cell_value_f15 = ws['F15']
        service_provider_cell_value_f15.value = "代理商"
        service_provider_cell_value_f15.font = Font(name='宋体', size=9, color="FF000000")
        service_provider_cell_value_f15.alignment = Alignment(horizontal='center', vertical='center')

        service_agent_name_cell = ws['I15']
        service_agent_name_cell.value = "服务代理商名称 "
        service_agent_name_cell.font = Font(name='宋体', size=9, color="FFFF0000", bold=True)
        service_agent_name_cell.alignment = Alignment(horizontal='center', vertical='center')

        ws.merge_cells("J15:N15")
        service_agent_name_cell_value = ws['J15']
        service_agent_name_cell_value.value = f"{service_agent_name}"
        service_agent_name_cell_value.font = Font(name='宋体', size=9, color="FF000000")
        service_agent_name_cell_value.alignment = Alignment(horizontal='center', vertical='center')
        # 设置第十六行

        # 设置第十七行
        recommended_machines_cell = ws['B17']
        recommended_machines_cell.value = "推荐机器情况"
        recommended_machines_cell.font = Font(name='宋体', size=9, color="FF000000", bold=True)

        # 设置颜色背景填充
        orange_fill = PatternFill(start_color="FCD5B4",
                                  end_color="FCD5B4",
                                  fill_type="solid")
        recommended_machines_cell.fill = orange_fill

        # 设置第十八行
        row18_brand_cell = ws['B18']
        row18_brand_cell.value = "品牌 "
        row18_brand_cell.font = Font(name='宋体', size=9, color="FFFF0000", bold=True)
        row18_brand_cell.alignment = Alignment(horizontal='center', vertical='center')

        row18_brand_cell_value_c18 = ws['C18']
        row18_brand_cell_value_c18.value = "（ ☑ ）"
        row18_brand_cell_value_c18.font = Font(name='宋体', size=9, color="FF000000")
        row18_brand_cell_value_c18.alignment = Alignment(horizontal='center', vertical='center')
        row18_brand_cell_value_d18 = ws['D18']
        row18_brand_cell_value_d18.value = "理光"
        row18_brand_cell_value_d18.font = Font(name='宋体', size=9, color="FF000000")
        row18_brand_cell_value_d18.alignment = Alignment(horizontal='center', vertical='center')
        row18_brand_cell_value_e18 = ws['E18']
        row18_brand_cell_value_e18.value = "（  ）"
        row18_brand_cell_value_e18.font = Font(name='宋体', size=9, color="FF000000")
        row18_brand_cell_value_e18.alignment = Alignment(horizontal='center', vertical='center')
        row18_brand_cell_value_f18 = ws['F18']
        row18_brand_cell_value_f18.value = "基士得耶"
        row18_brand_cell_value_f18.font = Font(name='宋体', size=9, color="FF000000")
        row18_brand_cell_value_f18.alignment = Alignment(horizontal='center', vertical='center')

        # 设置第十九行
        ws.merge_cells('B19:H19')
        row19_cell = ws['B19']
        row19_cell.value = "注意:Seg2机型 5台以下无需报备，可直接销售"
        row19_cell.font = Font(name='宋体', size=9, color="FF00B0F0", bold=True)
        # row19_cell.alignment = Alignment(horizontal='center', vertical='center')

        # 设置第二十行
        row20_prepare_recommended_models_accessories_cell = ws['B20']
        row20_prepare_recommended_models_accessories_cell.value = "准备推荐机型/配件"
        row20_prepare_recommended_models_accessories_cell.font = Font(name='宋体', size=9, color="FFFF0000", bold=True)
        row20_prepare_recommended_models_accessories_cell.alignment = Alignment(horizontal='center', vertical='center')
        ws.merge_cells('C20:H20')
        row20_prepare_recommended_models_accessories_cell_value = ws['C20']
        row20_prepare_recommended_models_accessories_cell_value.value = f"{row20_prepare_recommended_models_accessories}"
        row20_prepare_recommended_models_accessories_cell_value.font = Font(name='宋体', size=9, color="FF000000")
        row20_prepare_recommended_models_accessories_cell_value.alignment = Alignment(wrap_text=True,
                                                                                      horizontal='center',
                                                                                      vertical='center')

        # 设置第二十一行
        row21_number_cell = ws['B21']
        row21_number_cell.value = "台数"
        row21_number_cell.font = Font(name='宋体', size=9, color="FFFF0000", bold=True)
        row21_number_cell.alignment = Alignment(horizontal='center', vertical='center')

        row21_number_cell_value = ws['C21']
        row21_number_cell_value.value = f"{row21_number}"
        row21_number_cell_value.font = Font(name='宋体', size=9, color="FF000000")
        row21_number_cell_value.alignment = Alignment(horizontal='center', vertical='center')
        # 设置第二十二行
        row21_amount_cell = ws['B22']
        row21_amount_cell.value = "预测金额"
        row21_amount_cell.font = Font(name='宋体', size=9, color="FFFF0000", bold=True)
        row21_amount_cell.alignment = Alignment(horizontal='center', vertical='center')

        row21_amount_cell_value = ws['C22']
        row21_amount_cell_value.value = f"{row21_amount}"
        row21_amount_cell_value.font = Font(name='宋体', size=9, color="FF000000")
        row21_amount_cell_value.alignment = Alignment(horizontal='center', vertical='center')

        # 设置机型类别单元格，合并J18：N22
        ws.merge_cells('I18:I22')
        machine_type_cell = ws['I18']
        machine_type_cell.value = "机型类别"
        machine_type_cell.font = Font(name='宋体', size=9, color="FFFF0000", bold=True)
        machine_type_cell.alignment = Alignment(horizontal='center', vertical='center')

        machine_type_cell_value_j18 = ws['J18']
        machine_type_cell_value_j18.value = "彩色复印机"
        machine_type_cell_value_j18.font = Font(name='宋体', size=9, color="FF000000")
        machine_type_cell_value_j18.alignment = Alignment(horizontal='center', vertical='center')
        machine_type_cell_value_k18 = ws['K18']
        machine_type_cell_value_k18.value = "（ ☑ ）"
        machine_type_cell_value_k18.font = Font(name='宋体', size=9, color="FF000000")
        machine_type_cell_value_k18.alignment = Alignment(horizontal='center', vertical='center')
        machine_type_cell_value_l18 = ws['L18']
        machine_type_cell_value_l18.value = "复印机"
        machine_type_cell_value_l18.font = Font(name='宋体', size=9, color="FF000000")
        machine_type_cell_value_l18.alignment = Alignment(horizontal='center', vertical='center')
        machine_type_cell_value_m18 = ws['M18']
        machine_type_cell_value_m18.value = "（ ☑ ）"
        machine_type_cell_value_m18.font = Font(name='宋体', size=9, color="FF000000")
        machine_type_cell_value_m18.alignment = Alignment(horizontal='center', vertical='center')

        machine_type_cell_value_j19 = ws['J19']
        machine_type_cell_value_j19.value = "数码印刷机"
        machine_type_cell_value_j19.font = Font(name='宋体', size=9, color="FF000000")
        machine_type_cell_value_j19.alignment = Alignment(horizontal='center', vertical='center')
        machine_type_cell_value_k19 = ws['K19']
        machine_type_cell_value_k19.value = "（  ）"
        machine_type_cell_value_k19.font = Font(name='宋体', size=9, color="FF000000")
        machine_type_cell_value_k19.alignment = Alignment(horizontal='center', vertical='center')
        machine_type_cell_value_l19 = ws['L19']
        machine_type_cell_value_l19.value = "中高端激光打印机"
        machine_type_cell_value_l19.font = Font(name='宋体', size=9, color="FF000000")
        machine_type_cell_value_l19.alignment = Alignment(horizontal='center', vertical='center')
        machine_type_cell_value_m19 = ws['M19']
        machine_type_cell_value_m19.value = "（  ）"
        machine_type_cell_value_m19.font = Font(name='宋体', size=9, color="FF000000")
        machine_type_cell_value_m19.alignment = Alignment(horizontal='center', vertical='center')

        machine_type_cell_value_j20 = ws['J20']
        machine_type_cell_value_j20.value = "低端打印机"
        machine_type_cell_value_j20.font = Font(name='宋体', size=9, color="FF000000")
        machine_type_cell_value_j20.alignment = Alignment(horizontal='center', vertical='center')
        machine_type_cell_value_k20 = ws['K20']
        machine_type_cell_value_k20.value = "（  ）"
        machine_type_cell_value_k20.font = Font(name='宋体', size=9, color="FF000000")
        machine_type_cell_value_k20.alignment = Alignment(horizontal='center', vertical='center')
        machine_type_cell_value_l20 = ws['L20']
        machine_type_cell_value_l20.value = "互动电子液晶显示器"
        machine_type_cell_value_l20.font = Font(name='宋体', size=9, color="FF000000")
        machine_type_cell_value_l20.alignment = Alignment(horizontal='center', vertical='center')
        machine_type_cell_value_m20 = ws['M20']
        machine_type_cell_value_m20.value = "（  ）"
        machine_type_cell_value_m20.font = Font(name='宋体', size=9, color="FF000000")
        machine_type_cell_value_m20.alignment = Alignment(horizontal='center', vertical='center')

        machine_type_cell_value_j21 = ws['J21']
        machine_type_cell_value_j21.value = "纺织直喷打印机"
        machine_type_cell_value_j21.font = Font(name='宋体', size=9, color="FF000000")
        machine_type_cell_value_j21.alignment = Alignment(horizontal='center', vertical='center')
        machine_type_cell_value_k21 = ws['K21']
        machine_type_cell_value_k21.value = "（  ）"
        machine_type_cell_value_k21.font = Font(name='宋体', size=9, color="FF000000")
        machine_type_cell_value_k21.alignment = Alignment(horizontal='center', vertical='center')
        machine_type_cell_value_l21 = ws['L21']
        machine_type_cell_value_l21.value = " Solution"
        machine_type_cell_value_l21.font = Font(name='宋体', size=9, color="FF000000")
        machine_type_cell_value_l21.alignment = Alignment(horizontal='center', vertical='center')
        machine_type_cell_value_m21 = ws['M21']
        machine_type_cell_value_m21.value = "（  ）"
        machine_type_cell_value_m21.font = Font(name='宋体', size=9, color="FF000000")
        machine_type_cell_value_m21.alignment = Alignment(horizontal='center', vertical='center')

        # 设置第二十三行
        row23_delivery_date_cell = ws['B23']
        row23_delivery_date_cell.value = "预定交货日"
        row23_delivery_date_cell.font = Font(name='宋体', size=9, color="FFFF0000", bold=True)
        row23_delivery_date_cell.alignment = Alignment(horizontal='center', vertical='center')

        row23_delivery_date_cell_value = ws['C23']
        row23_delivery_date_cell_value.value = f"{row23_delivery_date}"
        row23_delivery_date_cell_value.font = Font(name='宋体', size=9, color="FF000000")
        row23_delivery_date_cell_value.alignment = Alignment(horizontal='center', vertical='center')

        row23_declarations_number_cell = ws['I23']
        row23_declarations_number_cell.value = "申报次数"
        row23_declarations_number_cell.font = Font(name='宋体', size=9, color="FFFF0000", bold=True)
        row23_declarations_number_cell.alignment = Alignment(horizontal='center', vertical='center')

        ws.merge_cells("J23:N23")
        row23_declarations_number_cell_value = ws['J23']
        row23_declarations_number_cell_value.value = f"{row23_declarations_number}"
        row23_declarations_number_cell_value.font = Font(name='宋体', size=9, color="FF000000")
        row23_declarations_number_cell_value.alignment = Alignment(horizontal='center', vertical='center')

        # 设置第二十四行

        # 设置第二十五行
        remarks_cell = ws['B25']
        remarks_cell.value = "附件与备注"
        remarks_cell.font = Font(name='宋体', size=9, color="FF000000", bold=True)
        remarks_cell.alignment = Alignment(horizontal='center', vertical='center')

        # 设置第二十六行
        ws.merge_cells("B26:N26")
        remarks_cell_value = ws['B26']
        remarks_cell_value.value = f"{remarks}"
        remarks_cell_value.font = Font(name='宋体', size=9, color="FF000000")
        remarks_cell_value.alignment = Alignment(wrap_text=True, horizontal='center', vertical='center')

        # 定义边框样式
        thin_border = Side(style='thin')
        no_border = Side(style=None)

        # 1. 设置完整边框的单元格区域（全部边框线）
        full_border_cells = [
            *[f'B{i}' for i in range(2, 11)],  # B2:B10
            *[f'B{i}' for i in range(13, 16)],  # B13:B15
            'B18', *[f'B{i}' for i in range(20, 24)],  # B20:B23
            *[f'I{i}' for i in range(6, 11)],  # I6:I10
            *[f'I{i}' for i in range(13, 16)],  # I13:I15
            'I23'
        ]

        for cell_ref in full_border_cells:
            cell = ws[cell_ref]
            cell.border = Border(left=thin_border, right=thin_border,
                                 top=thin_border, bottom=thin_border)

        # 2. 设置外边框线（无内边框）的合并区域
        def set_outer_border(range_str):
            """为合并单元格设置外边框"""
            min_col, min_row, max_col, max_row = range_boundaries(range_str)
            for row in range(min_row, max_row + 1):
                for col in range(min_col, max_col + 1):
                    cell = ws.cell(row=row, column=col)
                    new_border = Border(
                        left=thin_border if col == min_col else no_border,
                        right=thin_border if col == max_col else no_border,
                        top=thin_border if row == min_row else no_border,
                        bottom=thin_border if row == max_row else no_border
                    )
                    cell.border = new_border

        # 需要设置外边框的合并区域列表
        outer_border_ranges = [
            'C2:N2', 'C3:N3', 'C4:N4', 'C5:N5',
            'C6:H6', 'C7:H7', 'C8:H8', 'C9:H9', 'C10:H10',
            'C13:H13', 'C14:H14', 'C15:H15',
            'C18:H18', 'C20:H20', 'C21:H21', 'C22:H22', 'C23:H23',
            'J6:N6', 'J7:N7', 'J8:N8', 'J9:N9', 'J10:N10',
            'J13:N13', 'J14:N14', 'J15:N15', 'J23:N23',
            'B2:H2', 'I18:I22', 'J18:N22', 'B26:N26'
        ]

        # 为所有合并区域设置外边框
        for range_str in outer_border_ranges:
            set_outer_border(range_str)

        b2_cell = ws['B2']
        b2_cell.border = Border(left=thin_border, right=thin_border,
                                top=thin_border, bottom=thin_border)

        new_latest_customer_name = re.sub(r'[\\/*?:<>|,.，。、"]', '_', latest_customer_name)
        wb.save(
            f"{self.provision_excel_file_dir_path}/{provision_date}+{brand}+{new_latest_customer_name}.xlsx")

# if __name__ == '__main__':
#     ProvisionExcelModel().to_excel_file()